package net.fxug.hokuriku.chocolat.dao.contents
{
    import flash.errors.IllegalOperationError;

    public class SQLQuery
    {
        public function SQLQuery()
        {
            throw new IllegalOperationError("このクラスは定数クラスです。インスタンス化は許可されていません。");
        }
		//---------------------------------------------------------------------//
		// CREATE系
		// FIXME ;でCREATE文をつないでも連続で実行できないので別途定義している。
		//---------------------------------------------------------------------//
        /** 初期DB生成用のSQL */
        public static const CREATE_TABLE_SESSION:String =
			"CREATE TABLE IF NOT EXISTS SESSION" +
			"	(" +
			"		SESSION_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
			"		START_DATETIME DATE NOT NULL," +
			"		TITLE TEXT," +
			"		SESSION_TAG TEXT," +
			"		ATND_ID INTEGER" +
			"	); ";
		/** 初期DB生成用のSQL */
		public static const CREATE_TABLE_VOTE:String = 
			"CREATE TABLE IF NOT EXISTS VOTE" +
			"	(" +
			"		SESSION_ID INTEGER NOT NULL," +
			"		USER_ID TEXT," +
			"		ROUTE TEXT," +
			"		DATETIME DATE NOT NULL," +
			"		TWEET TEXT," +
			"		PROTOCOL BOOLEAN " +
//			"		PRIMARY KEY" +
//			"			(" +
//			"				SESSION_ID," +
//			"				USER_ID," +
//			"				ROUTE," +
//			"				DATETIME" +
//			"			)" +
			"	); "; //+
//			"ALTER TABLE VOTE" +
//			"	ADD FOREIGN KEY (SESSION_ID)" +
//			"	REFERENCES SESSION (SESSION_ID)" +
//			"	ON UPDATE RESTRICT" +
//			"	ON DELETE RESTRICT";
		//---------------------------------------------------------------------//
		// SESSION系
		//---------------------------------------------------------------------//
		/** セッション情報を取得する */
		public static const SELECT_SESSION:String =
			"SELECT " +
			"	SESSION_ID as sessionId," +
			"	START_DATETIME as startDatetime," +
			"	TITLE as title," +
			"	SESSION_TAG as sessionTag," +
			"	ATND_ID as atndId " +
			"FROM" +
			"	SESSION;";
		/** セッション情報を登録する。 */
		public static const INSERT_SESSION:String =
			"INSERT INTO SESSION (" +
//			"	SESSION_ID," +		SESSION_IDはオートインクリメントする。
			"	START_DATETIME," +
			"	TITLE," +
			"	SESSION_TAG," +
			"	ATND_ID " +
			") values (" +
//			"	:sessionId," +
			"	:startDatetime," +
			"	:title," +
			"	:sessionTag," +
			"	:atndId" +
			");";
		//---------------------------------------------------------------------//
		// 投票テーブル関連
		//---------------------------------------------------------------------//
		/** 投票の取得 */
		public static const SELECT_VOTE:String =
			"SELECT " +
			"	SESSION_ID as sessionId," +
			"	USER_ID as userId," +
			"	ROUTE as route," +
			"	DATETIME as datetime," +
			"	PROTOCOL as protocol," +
			"	TWEET as tweet " +
			"FROM" +
			"	VOTE;";
		/** セッションID指定の投票の取得 */
		public static const SELECT_VOTE_BY_SESSION_ID:String =
			"SELECT " +
			"	USER_ID as userId, " +
			"	ROUTE as route, " +
			"	SESSION_ID as sessionId," +
			"	DATETIME as datetime," +
			"	PROTOCOL as protocol," +
			"	TWEET as tweet " +
			"FROM" +
			"	VOTE " +
			"WHERE" +
			"	SESSION_ID = :sessionId";
        /** 投票の登録 */
        public static const INSERT_VOTE:String =
			"INSERT INTO VOTE (" +
			"	SESSION_ID," +
			"	USER_ID, " +
			"	ROUTE, " +
			"	DATETIME," +
			"	TWEET," +
			"	PROTOCOL " +
			") values (" +
			"	:sessionId," +
			"	:userId," +
			"	:route, " +
			"	:datetime," +
			"	:tweet," +
			"	:protocol " +
			");";
    }
}